package com.igbeok.miscellaneous.hibernate;

import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javassist.expr.NewArray;

import org.apache.commons.lang3.time.DateUtils;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.junit.Test;

import com.igbeok.hibernate.base.dao.BaseDao;
import com.igbeok.miscellaneous.hibernate.entity.Product;
import com.igbeok.miscellaneous.hibernate.entity.Student;
import com.igbeok.miscellaneous.hibernate.entity.TFPaxGrid;
import com.igbeok.miscellaneous.hibernate.entity.TRole;

public class SqlQueryTest {

	/**
	 * student schema
	 */
	@Test
	public void namedQueryForListTest() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		BaseDao dao = new BaseDao(sessionFactory);
		Map<String, Long> params = new HashMap<String, Long>();
		params.put("student_id", new Long(133));
		List<Student> students = dao.namedQueryForList(Student.class, "getStudentInfo2", params);
		for(Student student : students){
			System.out.println(student.getStudentId()+" "+student.getFullName());
		}
		System.out.println(students.size());
	}
	/**
	 * student schema
	 */
	@Test
	public void sqlQueryForListTest() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		BaseDao dao = new BaseDao(sessionFactory);
		Map<String, String> params = new HashMap<String, String>();
		//params.put("userName", "Vivi");
//		String sql = "select * from h_user where user_name =:userName";
		final String tfpgToday = "select v.* from TFP_VIEW v join OTF o on v.OF_ID = o.id where if_std between trunc(sysdate, 'dd') and to_date(to_char(trunc(sysdate, 'dd') + 1,'yyyy-mm-dd')||' 08:00:00', 'yyyy-mm-dd hh24:mi:ss') or if_ta  between trunc(sysdate, 'dd') and to_date(to_char(trunc(sysdate, 'dd') + 1,'yyyy-mm-dd')||' 08:00:00', 'yyyy-mm-dd hh24:mi:ss') or if_sta  between trunc(sysdate, 'dd') and to_date(to_char(trunc(sysdate, 'dd') + 1,'yyyy-mm-dd')||' 08:00:00', 'yyyy-mm-dd hh24:mi:ss') or v.of_td  between trunc(sysdate, 'dd') and to_date(to_char(trunc(sysdate, 'dd') + 1,'yyyy-mm-dd')||' 08:00:00', 'yyyy-mm-dd hh24:mi:ss') or o.of_std  between trunc(sysdate, 'dd') and to_date(to_char(trunc(sysdate, 'dd') + 1,'yyyy-mm-dd')||' 08:00:00', 'yyyy-mm-dd hh24:mi:ss')"+" and UPD_FLAG!='N'";
		List<TFPaxGrid> users = dao.queryForList(TFPaxGrid.class, tfpgToday, params);
//		for(TFPaxGrid user : users){
//			System.out.println(user.getUserName()+ " "+user.getCreateTime());
//		}
		System.out.println(users.size());
	}
	
	/**
	 * student schema
	 */
	@Test
	public void queryForPagedListTest() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		BaseDao dao = new BaseDao(sessionFactory);
		String queryStr = "select s.student_id, s.first_name, s.last_name, z.city from student s join zipcode z on s.zip = z.zip order by student_id";
		List<Student> students = dao.queryForPagedList(Student.class, queryStr, null,1,10);
		for(Student student : students){
			System.out.println(student.getStudentId()+" "+student.getFullName());
		}
		System.out.println(students.size());
	}



	/**
	 * 该方法需运行在store schema下
	 */
	@Test
	public void namedQueryForListTest2() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		BaseDao dao = new BaseDao(sessionFactory);
		List<Product> products = dao.namedQueryForList(Product.class, "getProductTypeInfo", null);
		for(Product product : products){
			System.out.println(product.getProductId()+" "+product.getProductTypeId()+" "+product.getProductTypeName());
		}
		System.out.println(products.size());
	}

	
	/**
	 * student schema
	 */
	@Test
	public void queryForNamedPagedListTest() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		BaseDao dao = new BaseDao(sessionFactory);
		Map<String, Long> params = new HashMap<String, Long>();
		params.put("student_id", new Long(133));
		List<Student> students = dao.namedQueryForPagedList(Student.class, "getStudentInfo2", params,1,10);
		for(Student student : students){
			System.out.println(student.getStudentId()+" "+student.getFullName());
		}
		System.out.println(students.size());
	}
	
	
	/**
	 * student schema
	 */
	@Test
	public void namedQueryforObjectTest() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		BaseDao dao = new BaseDao(sessionFactory);
		Map<String, Long> params = new HashMap<String, Long>();
		params.put("student_id", new Long(133));
		Student student = dao.namedQueryForObject(Student.class, "getStudentInfo2", params);
		System.out.println(student.getStudentId()+" "+student.getFirstName()+" "+student.getLastName());
	}
	
	@Test
	public void multipalParametersTest() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		BaseDao dao = new BaseDao(sessionFactory);
		Map<String, Date> params = new HashMap<String, Date>();
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.MILLISECOND, 0);
		calendar.set(Calendar.SECOND, 0);
		calendar.set(Calendar.MINUTE, 0);
		calendar.set(Calendar.HOUR_OF_DAY, 0);
		Date today = calendar.getTime();
		Date beginDate = DateUtils.addDays(today, -2);
		Date endDate = DateUtils.addDays(today, -1);
		params.put("beginDate", beginDate);
		params.put("endDate", endDate);
		params.put("beginDate2", beginDate);
		params.put("endDate2", endDate);
		params.put("beginDate3", beginDate);
		params.put("endDate3", endDate);
		params.put("beginDate4", beginDate);
		params.put("endDate4", endDate);
		params.put("beginDate5", beginDate);
		params.put("endDate5", endDate);
		String sqlTemplate = "select v.*, o.of_std, "
				+ "trunc(v.if_ta, 'dd') -trunc(sysdate,'dd') as in_relative_day, "
				+ "trunc(v.of_td, 'dd') -trunc(sysdate,'dd') as out_relative_day "
				+ "from TFP_VIEW v join OTF o "
				+ "on v.OF_ID = o.id ";
		String sql = sqlTemplate + "where if_std >= :beginDate and if_std< :endDate "
				+ "or if_ta >= :beginDate5 and if_ta< :endDate5 "
				+ "or if_sta >= :beginDate2 and if_sta< :endDate2 "
				+ "or v.of_td >= :beginDate3 and v.of_td< :endDate3 "
				+ "or o.of_std >= :beginDate4 and if_std< :endDate4";
		List<TFPaxGrid> tfPaxGrids = dao.queryForList(TFPaxGrid.class, sql, params);
		System.out.println(tfPaxGrids.size());
	}
	
	@Test
	public void saveTest() { 
		Configuration cfg = new Configuration().configure();// 读取hibernate.cfg.xml
		SessionFactory sessionFactory = cfg.buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		Transaction tx = session.beginTransaction();
		TRole role = new TRole();
		role.setRoleName("asdasd");
		session.save(role);
		tx.commit();
		
	}
}
